 1.电商分析之--核心交易之ADS层开发
   
   需求：计算当天
        全国所有订单信息
        全国、一级商品分类订单信息
        全国、二级商品分类订单信息
        大区所有订单信息
        大区、一级商品分类订单信息
        大区、二级商品分类订单信息
        城市所有订单信息
        城市、一级商品分类订单信息
        城市、二级商品分类订单信息
   用到的表：
       dws.dws_trade_orders_w
   1).ADS层建表
   -- ADS层订单分析表
   DROP TABLE IF EXISTS ads.ads_trade_order_analysis;
create table if not exists ads.ads_trade_order_analysis(
areatype string, -- 区域范围：区域类型（全国、大区、城市）
regionname string, -- 区域名称
cityname string, -- 城市名称
categorytype string, -- 商品分类类型（一级、二级）
category1 string, -- 商品一级分类名称
category2 string, -- 商品二级分类名称
totalcount bigint, -- 订单数量
total_productnum bigint, -- 商品数量
totalmoney double -- 支付金额
)
partitioned by (dt string)
row format delimited fields terminated by ',';
   
   2).ADS层加载数据
   /data/lagoudw/script/trade/ads_load_trade_order_analysis.sh
   
   备注：1笔订单，有多个商品；多个商品有不同的分类；这会导致一笔订单有多个分
类，它们是分别统计的；

#！/bin/bash

source /etc/profile

if [ -n "$1" ]
then
do_date=$1
else
do_date=`date -d "-1 day" +%F`
fi

sql="
with mid_orders as (
select regionname,
cityname,
firstname category1,
secondname category2,
count(distinct orderid) as totalcount,
sum(productsnum) as total_productnum,
sum(paymoney) as totalmoney
from dws.dws_trade_orders_w
where dt='$do_date'
group by regionname, cityname, firstname, secondname
)
insert overwrite table ads.ads_trade_order_analysis
partition(dt='$do_date')
select '全国' as areatype,
'' as regionname,
'' as cityname,
'' as categorytype,
'' as category1,
'' as category2,
sum(totalcount),
sum(total_productnum),
sum(totalmoney)
from mid_orders

union all

select '全国' as areatype,
'' as regionname,
'' as cityname,
'一级' as categorytype,
category1,
'' as category2,
sum(totalcount),
sum(total_productnum),
sum(totalmoney)
from mid_orders
group by category1

union 

select '全国' as areatype,
'' as regionname,
'' as cityname,
'二级' as categorytype,
'' as category1,
category2,
sum(totalcount),
sum(total_productnum),
sum(totalmoney)
from mid_orders
group by category2

union all

select '大区' as areatype,
regionname,
'' as cityname,
'' as categorytype,
'' as category1,
'' as category2,
sum(totalcount),
sum(total_productnum),
sum(totalmoney)
from mid_orders
group by regionname

union all

select '大区' as areatype,
regionname,
'' as cityname,
'一级' as categorytype,
category1,
'' as category2,
sum(totalcount),
sum(total_productnum),
sum(totalmoney)
from mid_orders
group by regionname, category1

union all

select '大区' as areatype,
regionname,
'' as cityname,
'二级' as categorytype,
'' as category1,
category2,
sum(totalcount),
sum(total_productnum),
sum(totalmoney)
from mid_orders
group by regionname, category2

union all

select '城市' as areatype,
'' as regionname,
cityname,
'' as categorytype,
'' as category1,
'' as category2,
sum(totalcount),
sum(total_productnum),
sum(totalmoney)
from mid_orders
group by cityname

union all

select '城市' as areatype,
'' as regionname,
cityname,
'一级' as categorytype,
category1,
'' as category2,
sum(totalcount),
sum(total_productnum),
sum(totalmoney)
from mid_orders
group by cityname, category1

union all

select '城市' as areatype,
'' as regionname,
cityname,
'二级' as categorytype,
'' as category1,
category2,
sum(totalcount),
sum(total_productnum),
sum(totalmoney)
from mid_orders
group by cityname, category2;
"

hive -e "$sql"
   
 2.小结
   
   脚本调用次序：
   # 加载ODS数据（含DataX迁移数据）
   /data/lagoudw/script/trade/ods_load_trade.sh
   # 加载DIM层数据
   /data/lagoudw/script/trade/dim_load_product_cat.sh
   /data/lagoudw/script/trade/dim_load_shop_org.sh
   /data/lagoudw/script/trade/dim_load_payment.sh
   /data/lagoudw/script/trade/dim_load_product_info.sh
   # 加载DWD层数据
   /data/lagoudw/script/trade/dwd_load_trade_orders.sh
   # 加载DWS层数据
   /data/lagoudw/script/trade/dws_load_trade_orders.sh
   # 加载ADS层数据
   /data/lagoudw/script/trade/ads_load_trade_order_analysis.sh
   
   主要技术点：
   拉链表。创建、使用与回滚；商品信息表、订单表(周期性事实表；分区表+拉链表)
   宽表(逆规范化)：商品分类表、商品地域组织表、订单明细及订单明细宽表
(轻度汇总的事实表)